﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;

namespace WindowsFormsApplication1
{
    class dbutils
    {
        public static string constr = "server=localhost;User Id=root;password=;Database=test";
        MySqlConnection mycon = new MySqlConnection("server=localhost;User Id=root;password=;Database=test");
        MySqlDataAdapter adapter = new MySqlDataAdapter();

        public string loginsql(string sql){

            try
            {
                mycon.Open();
                MySqlCommand mycmd = new MySqlCommand(sql, mycon);
                adapter.SelectCommand = mycmd;
                DataSet myds = new DataSet();
                adapter.Fill(myds, "data");
                int result = myds.Tables[0].Rows.Count;
                string name=myds.Tables[0].Rows[0][0].ToString();
                mycon.Close();
                if (result == 0)
                    return null;
                return name;
            }
            catch (Exception e)
            {
                mycon.Close();
                return null;
            }
        }

        public int select(string sql)
        {
            try
            {
                mycon.Open();
                MySqlCommand mycmd = new MySqlCommand(sql, mycon);
                adapter.SelectCommand = mycmd;
                DataSet myds = new DataSet();
                adapter.Fill(myds, "data");
                int result = myds.Tables[0].Rows.Count;
                //string name = myds.Tables[0].Rows[0][0].ToString();
                mycon.Close();
                return result;
            }
            catch (MySqlException e)
            {
                mycon.Close();
                return 0;
            }

        }

        public string select_one(string sql,int n)
        {
            try
            {
                mycon.Open();
                MySqlCommand mycmd = new MySqlCommand(sql, mycon);
                adapter.SelectCommand = mycmd;
                DataSet myds = new DataSet();
                adapter.Fill(myds, "data");
                int result = myds.Tables[0].Rows.Count;
                string name = myds.Tables[0].Rows[0][n].ToString();
                mycon.Close();
                if (result == 0)
                    return null;
                return name;
            }
            catch (Exception e)
            {
                mycon.Close();
                return null;
            }

        }
        public List<string> select_staffid_set()
        {
            string sql = "select loginname from staff";
            List<string> staffids = new List<string>();
            try
            {
                mycon.Open();
                MySqlCommand mycmd = new MySqlCommand(sql, mycon);
                adapter.SelectCommand = mycmd;
                DataSet myds = new DataSet();
                adapter.Fill(myds, "data");
                int result = myds.Tables[0].Rows.Count;
                if (result == 0)
                    return null;
                for (int i = 0; i < result; i++)
                {
                    staffids.Add(myds.Tables[0].Rows[i][0].ToString());
                }
                mycon.Close();
                return staffids;
            }
            catch (Exception e)
            {
                mycon.Close();
                return null;
            }

            return null;
        }

        public string select_multi(string sql, int n)
        {
            try
            {
                mycon.Open();
                MySqlCommand mycmd = new MySqlCommand(sql, mycon);
                adapter.SelectCommand = mycmd;
                DataSet myds = new DataSet();
                adapter.Fill(myds, "data");
                int result = myds.Tables[0].Rows.Count;
                string name="";
                for (int i = 0; i < n; i++)
                {
                    name += myds.Tables[0].Rows[0][i].ToString() + "+";
                }
                mycon.Close();
                if (result == 0)
                    return null;
                return name;
            }
            catch (Exception e)
            {
                mycon.Close();
                return null;
            }

        }

        public int insert(string sql)
        {
            try
            {
                mycon.Open();
                MySqlCommand mycmd = new MySqlCommand(sql, mycon);
                int re= mycmd.ExecuteNonQuery();
                mycon.Close();
                return re;
            }
            catch (MySqlException e)
            {
                mycon.Close();
                return 0;
            }
        }

            
    }
}
